package giee.com.sqltransfer.ToExcel;
import org.apache.commons.io.input.BOMInputStream;
import javax.swing.*;
import javax.swing.border.Border;
import javax.swing.border.TitledBorder;
import java.awt.*;
import java.awt.datatransfer.DataFlavor;
import java.awt.datatransfer.Transferable;
import java.awt.dnd.*;
import java.awt.event.*;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 数据库信息查询工具和SQL解析器的主界面类
 * 功能：支持打开、拖放SQL文件，解析SQL语句并显示结果
 */
public class main extends JFrame {

    // 组件声明
    private JTextArea sqlTextArea;      // SQL内容输入/显示区域
    private JButton openFileButton;     // 打开文件按钮
    private JButton parseButton;        // 解析SQL按钮
    private String SqlFileName;
    /**
     * 构造函数
     */
    public main() {
        initUI();               // 初始化UI组件
        setupWindowProperties(); // 设置窗口属性
    }

    /**
     * 设置窗口基本属性
     */
    private void setupWindowProperties() {
        setTitle("数据库信息查询工具 + SQL解析器");
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        setSize(1200, 800);
        setLocationRelativeTo(null); // 居中显示
    }

    /**
     * 初始化UI界面
     */
    private void initUI() {
        initSQLComponents();    // 初始化SQL相关组件
        layoutComponents();     // 布局组件
        setupDragAndDrop();     // 设置拖放功能
    }

    /**
     * 初始化SQL解析相关组件
     */
    private void initSQLComponents() {
        // 初始化SQL文本区域
        sqlTextArea = new JTextArea();
        sqlTextArea.setFont(new Font("Monospaced", Font.PLAIN, 14)); // 使用等宽字体
        sqlTextArea.setToolTipText("可以直接拖放SQL文件到这里");
        setupTextAreaBorder();  // 设置边框样式

        // 初始化按钮
        openFileButton = new JButton("打开SQL文件");
        parseButton = new JButton("解析SQL");

        // 添加按钮事件监听
        openFileButton.addActionListener(e -> openSQLFile());
        parseButton.addActionListener(e -> parseSQL());
    }

    /**
     * 设置文本区域边框样式
     */
    private void setupTextAreaBorder() {
        sqlTextArea.setBorder(BorderFactory.createLineBorder(Color.GRAY));
        sqlTextArea.addMouseListener(new MouseAdapter() {
            @Override
            public void mouseEntered(MouseEvent e) {
                sqlTextArea.setBorder(BorderFactory.createLineBorder(Color.BLUE, 2));
            }

            @Override
            public void mouseExited(MouseEvent e) {
                sqlTextArea.setBorder(BorderFactory.createLineBorder(Color.GRAY));
            }
        });
    }

    /**
     * 布局主界面组件
     */
    private void layoutComponents() {
        JPanel mainPanel = createMainPanel();
        mainPanel.add(createSQLParserPanel(), BorderLayout.CENTER);
        add(mainPanel);
    }

    /**
     * 创建主面板
     * @return 主面板
     */
    private JPanel createMainPanel() {
        JPanel panel = new JPanel(new BorderLayout(5, 5));
        panel.setBorder(BorderFactory.createEmptyBorder(15, 15, 15, 15)); // 设置内边距
        return panel;
    }

    /**
     * 创建带标题和边距的边框
     * @param title 边框标题
     * @return 边框对象
     */
    private Border createTitledBorderWithPadding(String title) {
        return BorderFactory.createCompoundBorder(
                new TitledBorder(title),
                BorderFactory.createEmptyBorder(15, 15, 15, 15)
        );
    }

    /**
     * 创建SQL解析器面板
     * @return SQL解析器面板
     */
    private JPanel createSQLParserPanel() {
        JPanel panel = new JPanel(new BorderLayout(5, 5));
        panel.setBorder(createTitledBorderWithPadding("SQL解析器"));

        panel.add(createSQLInputPanel(), BorderLayout.CENTER); // 添加SQL输入区域
        panel.add(createButtonPanel(), BorderLayout.NORTH);    // 添加按钮区域

        return panel;
    }

    /**
     * 创建SQL输入面板（带滚动条）
     * @return 滚动面板
     */
    private JScrollPane createSQLInputPanel() {
        JScrollPane scrollPane = new JScrollPane(sqlTextArea);
        scrollPane.setBorder(new TitledBorder("SQL内容"));
        return scrollPane;
    }

    /**
     * 创建按钮面板
     * @return 按钮面板
     */
    private JPanel createButtonPanel() {
        JPanel panel = new JPanel(new FlowLayout(FlowLayout.CENTER, 15, 5));
        panel.add(openFileButton);
        panel.add(parseButton);
        return panel;
    }

    /**
     * 设置拖放功能
     */
    private void setupDragAndDrop() {
        // 创建拖放目标
        DropTarget dropTarget = new DropTarget(sqlTextArea, new DropTargetAdapter() {
            @Override
            public void drop(DropTargetDropEvent dtde) {
                try {
                    dtde.acceptDrop(DnDConstants.ACTION_COPY); // 接受拖放操作
                    Transferable transferable = dtde.getTransferable();

                    // 获取拖放的文件列表
                    @SuppressWarnings("unchecked")
                    List<File> files = (List<File>) transferable.getTransferData(DataFlavor.javaFileListFlavor);

                    if (files != null && !files.isEmpty()) {
                        File file = files.get(0);
                        if (file.getName().toLowerCase().endsWith(".sql")) {
                            // 读取SQL文件内容并去除注释
                            SqlFileName = file.getName().replace(".sql","");        // 获取sql文件的名称
                            String content = removeComments(new BufferedReader(
                                    new InputStreamReader(new FileInputStream(file), StandardCharsets.UTF_8)));
                            sqlTextArea.setText(content);
                        } else {
                            showErrorMessage("只支持.sql文件");
                        }
                    }
                } catch (Exception ex) {
                    showErrorMessage("读取文件时出错: " + ex.getMessage());
                }
            }
        });

        sqlTextArea.setDropTarget(dropTarget);
        sqlTextArea.setTransferHandler(new TransferHandler() {
            @Override
            public boolean canImport(TransferSupport support) {
                return support.isDataFlavorSupported(DataFlavor.javaFileListFlavor);
            }

            @Override
            public boolean importData(TransferSupport support) {
                if (!canImport(support)) {
                    return false;
                }

                try {
                    @SuppressWarnings("unchecked")
                    List<File> files = (List<File>) support.getTransferable()
                            .getTransferData(DataFlavor.javaFileListFlavor);

                    if (files != null && !files.isEmpty()) {
                        File file = files.get(0);
                        return file.getName().toLowerCase().endsWith(".sql");
                    }
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
                return false;
            }
        });
    }

    /**
     * 打开SQL文件
     */
    private void openSQLFile() {
        JFileChooser fileChooser = new JFileChooser();
        // 设置文件过滤器，只显示.sql文件和目录
        fileChooser.setFileFilter(new javax.swing.filechooser.FileFilter() {
            @Override
            public boolean accept(File f) {
                return f.isDirectory() || f.getName().toLowerCase().endsWith(".sql");
            }

            @Override
            public String getDescription() {
                return "SQL文件 (*.sql)";
            }
        });

        int returnValue = fileChooser.showOpenDialog(this);
        if (returnValue == JFileChooser.APPROVE_OPTION) {
            File selectedFile = fileChooser.getSelectedFile();
            SqlFileName = selectedFile.getName().replace(".sql","");    // 获取sql文件的名称

            try {
                // 使用BOMInputStream处理UTF-8 BOM头
                BOMInputStream bomInputStream = new BOMInputStream(new FileInputStream(selectedFile));

                // 自动检测文件编码
                String charsetName = detectCharset(selectedFile);

                // 读取文件内容并去除注释
                String content = removeComments(new BufferedReader(
                        new InputStreamReader(bomInputStream, charsetName)));

                sqlTextArea.setText(content);
            } catch (IOException ex) {
                showErrorMessage("读取文件时出错: " + ex.getMessage());
            }
        }
    }

    /**
     * 检测文件编码
     * @param file 要检测的文件
     * @return 检测到的编码名称
     * @throws IOException 如果读取文件出错
     */
    private String detectCharset(File file) throws IOException {
        // 默认使用UTF-8
        String charsetName = StandardCharsets.UTF_8.name();

        try (InputStream inputStream = new FileInputStream(file)) {
            byte[] buffer = new byte[4096];
            int read = inputStream.read(buffer);

            // 检查UTF-8 BOM标记
            if (read >= 3 && buffer[0] == (byte) 0xEF && buffer[1] == (byte) 0xBB && buffer[2] == (byte) 0xBF) {
                return StandardCharsets.UTF_8.name();
            }

            // 尝试UTF-8解码
            String content = new String(buffer, 0, read, StandardCharsets.UTF_8);
            if (!content.contains("�")) { // 如果没有乱码字符
                return StandardCharsets.UTF_8.name();
            }

            // 尝试GBK解码
            content = new String(buffer, 0, read, "GBK");
            if (!content.contains("�")) {
                return "GBK";
            }
        }

        return charsetName;
    }

    /**
     * 去除SQL注释
     * @param reader 文件读取器
     * @return 去除注释后的SQL内容
     * @throws IOException 如果读取出错
     */
    private String removeComments(BufferedReader reader) throws IOException {
        StringBuilder content = new StringBuilder();
        String line;
        boolean inBlockComment = false; // 是否在块注释中
        boolean inString = false;      // 是否在字符串中
        char stringChar = '\0';        // 字符串引号类型

        while ((line = reader.readLine()) != null) {
            StringBuilder cleanLine = new StringBuilder();

            for (int i = 0; i < line.length(); i++) {
                char c = line.charAt(i);

                if (inString) {
                    cleanLine.append(c);
                    // 检查字符串结束，考虑转义字符
                    if (c == stringChar && (i == 0 || line.charAt(i-1) != '\\')) {
                        inString = false;
                    }
                    continue;
                }

                if (!inBlockComment) {
                    if (c == '\'' || c == '"') {
                        inString = true;
                        stringChar = c;
                        cleanLine.append(c);
                        continue;
                    }

                    // 检查行注释
                    if (c == '-' && i + 1 < line.length() && line.charAt(i+1) == '-') {
                        break; // 忽略行剩余部分
                    }

                    // 检查块注释开始
                    if (c == '/' && i + 1 < line.length() && line.charAt(i+1) == '*') {
                        inBlockComment = true;
                        i++; // 跳过下一个字符
                        continue;
                    }

                    cleanLine.append(c);
                } else {
                    // 检查块注释结束
                    if (c == '*' && i + 1 < line.length() && line.charAt(i+1) == '/') {
                        inBlockComment = false;
                        i++; // 跳过下一个字符
                    }
                }
            }

            // 只有在不在块注释中且行不为空时才添加
            if (!inBlockComment && cleanLine.length() > 0) {
                content.append(cleanLine.toString()).append("\n");
            }
        }

        reader.close();
        return content.toString();
    }

    /**
     * 解析SQL语句，点击事件
     */
    private void parseSQL() {
        String sql = sqlTextArea.getText().trim();
        if (sql.isEmpty()) {
            showWarningMessage("没有SQL内容可解析");
            return;
        }

        try {
            // 分割SQL语句
            List<String> statements = splitSQLStatements(sql);
            List<SQLStatement> parsedStatements = new ArrayList<>();

            // 解析每条SQL语句
            for (String stmt : statements) {
                parsedStatements.add(parseStatement(stmt));
            }

            // 处理解析结果
            processSQL(parsedStatements);

        } catch (Exception ex) {
            showErrorMessage("解析SQL时出错: " + ex.getMessage());
        }
    }

    /**
     * 分割SQL语句（以分号分隔）
     * @param sql SQL内容
     * @return 分割后的SQL语句列表
     */
    private List<String> splitSQLStatements(String sql) {
        List<String> statements = new ArrayList<>();
        StringBuilder currentStatement = new StringBuilder();
        boolean inString = false;   // 是否在字符串中
        char stringChar = '\0';    // 字符串引号类型

        for (int i = 0; i < sql.length(); i++) {
            char c = sql.charAt(i);

            if (inString) {
                currentStatement.append(c);
                // 检查字符串结束，考虑转义字符
                if (c == stringChar) {
                    if (i > 0 && sql.charAt(i-1) == '\\') {
                        continue;
                    }
                    inString = false;
                }
                continue;
            }

            if (c == '\'' || c == '"') {
                inString = true;
                stringChar = c;
                currentStatement.append(c);
                continue;
            }

            if (c == ';') {
                String stmt = currentStatement.toString().trim();
                if (!stmt.isEmpty()) {
                    statements.add(stmt);
                }
                currentStatement.setLength(0); // 清空当前语句
            } else {
                currentStatement.append(c);
            }
        }

        // 添加最后一条语句（可能没有分号结尾）
        String lastStmt = currentStatement.toString().trim();
        if (!lastStmt.isEmpty()) {
            statements.add(lastStmt);
        }

        return statements;
    }

    /**
     * 解析SQL语句类型和相关信息
     * @param sql SQL语句
     * @return 解析后的SQLStatement对象
     */
    private SQLStatement parseStatement(String sql) {
        SQLStatement statement = new SQLStatement();
        statement.original = sql;

        String upperSQL = sql.toUpperCase();

        // 根据SQL语句类型调用不同的解析方法
        if(upperSQL.startsWith("CREATE DATABASE")) {
            statement.type = "CREATE DATABASE";
            parseCreateDatabaseStatement(statement,sql);
        } else if(upperSQL.startsWith("CREATE TABLE")) {
            statement.type = "CREATE TABLE";
            parseCreateTableStatement(statement, sql);
        } else if (upperSQL.startsWith("INSERT")) {
            statement.type = "INSERT";
            parseInsertStatement(statement, sql);
        } else if (upperSQL.startsWith("DROP")) {
            statement.type = "DROP";
            parseDropTableStatement(statement, sql);
        } else if (upperSQL.startsWith("LOCK")) {
            statement.type = "LOCK";
        } else if(upperSQL.startsWith("UNLOCK")) {
            statement.type = "UNLOCK";
        } else if(upperSQL.startsWith("SET")) {
            statement.type = "SET";
        } else if(upperSQL.startsWith("USE")) {
            statement.type = "USE";
        } else {
            statement.type = "UNKNOWN";
        }
        return statement;
    }

    /**
     * 解析CREATE DATABASE语句
     * @param statement SQLStatement对象
     * @param sql SQL语句
     */
    private void parseCreateDatabaseStatement(SQLStatement statement, String sql) {
        String normalizedSql = normalizeSQL(sql);

        Pattern pattern = Pattern.compile(
                "CREATE\\s+DATABASE\\s+(?:IF\\s+NOT\\s+EXISTS\\s+)?([`\"\\w]+)",
                Pattern.CASE_INSENSITIVE
        );

        Matcher matcher = pattern.matcher(normalizedSql);
        if (matcher.find()) {
            String dbName = cleanIdentifier(matcher.group(1));
            if (!dbName.isEmpty()) {
                statement.tables.add(dbName);
            }
        }
    }

    /**
     * 解析CREATE TABLE语句
     * @param statement SQLStatement对象
     * @param sql SQL语句
     */
    private void parseCreateTableStatement(SQLStatement statement, String sql) {
        String normalizedSql = normalizeSQL(sql);

        Pattern pattern = Pattern.compile(
                "CREATE\\s+TABLE\\s+(?:IF\\s+NOT\\s+EXISTS\\s+)?([`\"\\w]+(?:\\s*\\.\\s*[`\"\\w]+)?)",
                Pattern.CASE_INSENSITIVE
        );

        Matcher matcher = pattern.matcher(normalizedSql);
        if (matcher.find()) {
            String tableName = cleanIdentifier(matcher.group(1));
            statement.tables.add(tableName);
        }
    }

    /**
     * 解析INSERT语句
     * @param statement SQLStatement对象
     * @param sql SQL语句
     */
    private void parseInsertStatement(SQLStatement statement, String sql) {
        String normalizedSql = normalizeSQL(sql);

        Pattern pattern = Pattern.compile(
                "INSERT\\s+(?:INTO\\s+)?([`\"\\w]+(?:\\s*\\.\\s*[`\"\\w]+)?)",
                Pattern.CASE_INSENSITIVE
        );

        Matcher matcher = pattern.matcher(normalizedSql);
        if (matcher.find()) {
            String tableName = cleanIdentifier(matcher.group(1));
            statement.tables.add(tableName);
        }
    }

    /**
     * 解析DROP TABLE语句
     * @param statement SQLStatement对象
     * @param sql SQL语句
     */
    private void parseDropTableStatement(SQLStatement statement, String sql) {
        String normalizedSql = normalizeSQL(sql);

        Pattern pattern = Pattern.compile(
                "DROP\\s+TABLE\\s+(?:IF\\s+EXISTS\\s+)?((?:[`\"]?\\w+[`\"]?(?:\\s*\\.\\s*[`\"]?\\w+[`\"]?)?)(?:\\s*,\\s*(?:[`\"]?\\w+[`\"]?(?:\\s*\\.\\s*[`\"]?\\w+[`\"]?)?))*)",
                Pattern.CASE_INSENSITIVE
        );

        Matcher matcher = pattern.matcher(normalizedSql);
        if (matcher.find()) {
            String[] tables = matcher.group(1).split("\\s*,\\s*");
            for (String table : tables) {
                String cleanTable = cleanIdentifier(table);
                if (!cleanTable.isEmpty()) {
                    statement.tables.add(cleanTable);
                }
            }
        }
    }

    /**
     * 处理解析后的SQL语句
     * @param statements SQL语句列表
     */
    private void processSQL(List<SQLStatement> statements) {

        new preImport(statements,SqlFileName);

//        StringBuilder result = new StringBuilder();
//        result.append("========== SQL解析结果 ==========\n");
//        result.append("共解析出 ").append(statements.size()).append(" 条SQL语句\n\n");
//
//        for (int i = 0; i < statements.size(); i++) {
//            SQLStatement stmt = statements.get(i);
//            result.append("语句 #").append(i + 1).append(":\n");
//            result.append("类型: ").append(stmt.type).append("\n");
//
//            if (!stmt.tables.isEmpty()) {
//                result.append("涉及表: ").append(String.join(", ", stmt.tables)).append("\n");
//            }
//
//            result.append("原始SQL:\n").append(stmt.original).append("\n\n");
//        }
//
//        // 显示结果对话框
//        JTextArea resultArea = new JTextArea(result.toString());
//        resultArea.setEditable(false);
//        resultArea.setFont(new Font("Monospaced", Font.PLAIN, 14));
//
//        JScrollPane scrollPane = new JScrollPane(resultArea);
//        scrollPane.setPreferredSize(new Dimension(800, 600));
//
//        JOptionPane.showMessageDialog(
//                this,
//                scrollPane,
//                "SQL解析结果",
//                JOptionPane.PLAIN_MESSAGE
//        );
    }

    /**
     * 规范化SQL语句（去除注释和多余空格）
     * @param sql 原始SQL
     * @return 规范化后的SQL
     */
    private String normalizeSQL(String sql) {
        return sql.replaceAll("--.*", "")
                .replaceAll("/\\*.*?\\*/", "")
                .replaceAll("\\s+", " ")
                .trim();
    }

    /**
     * 清理标识符（去除引号）
     * @param identifier 标识符
     * @return 清理后的标识符
     */
    private String cleanIdentifier(String identifier) {
        return identifier.replaceAll("`", "")
                .replaceAll("\"", "")
                .trim();
    }

    /**
     * 显示错误消息对话框
     * @param message 错误消息
     */
    private void showErrorMessage(String message) {
        JOptionPane.showMessageDialog(
                this,
                message,
                "错误",
                JOptionPane.ERROR_MESSAGE
        );
    }

    /**
     * 显示警告消息对话框
     * @param message 警告消息
     */
    private void showWarningMessage(String message) {
        JOptionPane.showMessageDialog(
                this,
                message,
                "警告",
                JOptionPane.WARNING_MESSAGE
        );
    }

    /**
     * 主方法
     * @param args 命令行参数
     */
    public static void main(String[] args) {
        SwingUtilities.invokeLater(() -> new main().setVisible(true));
    }
}

